package com.bosc.automationui.repository;

import com.bosc.automationui.entity.ETLJob;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.Date;
import java.util.List;

public interface ETLJobRepository extends PagingAndSortingRepository<ETLJob, String> {

    @Query(value = "SELECT ej.* " +
            "FROM " +
            "job_history jh " + "RIGHT JOIN etl_job ej ON jh.jobid = ej.jobsessionid " +
            "WHERE " +
            "ej.last_txdate=SYSDATE " +
            "AND jh.status <> ej.last_jobstatus " +
            "OR ej.last_jobstatus IS NULL " +
            "OR (ej.last_jobstatus = 'Failed' " +
            "AND jh.status IS NULL)", nativeQuery = true)
    List<String> getJobStatusIncrement();

    @Query(value = "select\n" +
            "       t.hour,(select sum(sub.num)" +
            "       from (\n" +
            "           select substr(LAST_ENDTIME, 12, 2) as hour, count(1) as num\n" +
            "           from ETL_JOB\n" +
            "            WHERE SUBSTR(LAST_ENDTIME, 0, 10)=TO_CHAR(SYSDATE, 'yyyy-mm-dd')  \n" +
            "            AND SUBSTR(LAST_STARTTIME, 0, 10)=TO_CHAR(SYSDATE, 'yyyy-mm-dd') AND  \n" +
            "            LOWER(LAST_JOBSTATUS) = 'done'" +
            "           group by substr(LAST_ENDTIME, 12, 2)" +
            " ) sub\n" +
            "       where " +
            "       sub.hour <= t.hour)" +
            "       as total_num\n" +
            "       from (" +
            "       select substr(LAST_ENDTIME, 12, 2) as hour" +
            "       from ETL_JOB\n" +
            "       WHERE SUBSTR(LAST_ENDTIME, 0, 10)=TO_CHAR(SYSDATE, 'yyyy-mm-dd')  \n" +
            "            AND SUBSTR(LAST_STARTTIME, 0, 10)=TO_CHAR(SYSDATE, 'yyyy-mm-dd') AND  \n" +
            "            LOWER(LAST_JOBSTATUS) = 'done' \n" +
            "       group by substr(LAST_ENDTIME, 12, 2)\n" +
            "    )t  " + "order by t.hour", nativeQuery = true)
    List<String> getTodayTrending();

    @Query(value = "select\n" +
            "       t.hour,(select sum(sub.num)" +
            "       from (\n" +
            "           select substr(LAST_ENDTIME, 12, 2) as hour, count(1) as num\n" +
            "           from ETL_JOB\n" +
            "            WHERE SUBSTR(LAST_ENDTIME, 0, 10)=TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')  \n" +
            "            AND SUBSTR(LAST_STARTTIME, 0, 10)=TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd') AND  \n" +
            "            LOWER(LAST_JOBSTATUS) = 'done'" +
            "           group by substr(LAST_ENDTIME, 12, 2)" +
            " ) sub\n" +
            "       where " +
            "       sub.hour <= t.hour)" +
            "       as total_num\n" +
            "       from (" +
            "       select substr(LAST_ENDTIME, 12, 2) as hour" +
            "       from ETL_JOB\n" +
            "       WHERE SUBSTR(LAST_ENDTIME, 0, 10)=TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')  \n" +
            "            AND SUBSTR(LAST_STARTTIME, 0, 10)=TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd') AND  \n" +
            "            LOWER(LAST_JOBSTATUS) = 'done' \n" +
            "       group by substr(LAST_ENDTIME, 12, 2)\n" +
            "    )t  " + "order by t.hour", nativeQuery = true)
    List<String> getYesterdayTrending();

    //todo 加状态筛选

    @Query("SELECT ej FROM ETLJob ej WHERE SYSDATE - ej.lastTxDate > 2")
    List<ETLJob> getTPlus();
    //todo 更改逻辑仅过滤到目前时间大于一天的

    @Query(value = "SELECT * FROM etl_job WHERE SUBSTR(LAST_STARTTIME, 1, 10)=TO_CHAR(SYSDATE, 'yyyy-mm-dd')", nativeQuery = true)
    List<ETLJob> getAllToday();

    List findAllByOrderByLastTxDateDesc();

    @Query(value = "SELECT e from ETLJob e where UPPER(e.etlJob) like CONCAT('%',UPPER(?1),'%')")
    List findAllByEtlJob(String etlJob);

    @Query(value = "SELECT e from ETLJob e where UPPER(e.etlSystem) like CONCAT('%',UPPER(?1),'%')")
    List findAllByEtlSystem(String etlSystem);


    List findAllByLastTxDate(Date lastTxDate);


}
